﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace DelonixRegia.Classes.Reporting
{
    public class reportingDBManager
    {
        public static ArrayList getRoomByStatus(string room_status)
        {
            ArrayList roomlist = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Room WHERE roomStatus = @roomStatus";
                comm.Parameters.AddWithValue("@roomStatus", room_status);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Room room = new Room();
                    room.roomNo = (int)dr["roomNo"];
                    room.roomLevel = (int)dr["roomLevel"];
                    room.roomType = (int)dr["roomType"];
                    room.smoking = (bool)dr["smoking"];
                    room.balcony = (bool)dr["balcony"];
                    room.roomRate = (decimal)dr["roomRate"];
                    room.roomStatus = (string)dr["roomStatus"];
                    room.capacity = (int)dr["capacity"];
                    roomlist.Add(room);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            conn.Close();
            return roomlist;
        }

        public static ArrayList getRoomStatusbyLevel(int room_level)
        {
            ArrayList roomlist = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Room WHERE roomLevel = @room_level";
                comm.Parameters.AddWithValue("@room_level", room_level);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Room room = new Room();
                    room.roomNo = (int)dr["roomNo"];
                    room.roomLevel = (int)dr["roomLevel"];
                    room.roomType = (int)dr["roomType"];
                    room.smoking = (bool)dr["smoking"];
                    room.balcony = (bool)dr["balcony"];
                    room.roomRate = (decimal)dr["roomRate"];
                    room.roomStatus = (string)dr["roomStatus"];
                    room.capacity = (int)dr["capacity"];
                    roomlist.Add(room);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            conn.Close();
            return roomlist;
        }

        public static ArrayList getRoombyStatusAndLevel(int room_level, string room_status)
        {
            ArrayList roomlist = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Room WHERE roomLevel = @room_level AND roomStatus = @room_status";
                comm.Parameters.AddWithValue("@room_level", room_level);
                comm.Parameters.AddWithValue("@room_status", room_status);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Room room = new Room();
                    room.roomNo = (int)dr["roomNo"];
                    room.roomLevel = (int)dr["roomLevel"];
                    room.roomType = (int)dr["roomType"];
                    room.smoking = (bool)dr["smoking"];
                    room.balcony = (bool)dr["balcony"];
                    room.roomRate = (decimal)dr["roomRate"];
                    room.roomStatus = (string)dr["roomStatus"];
                    room.capacity = (int)dr["capacity"];
                    roomlist.Add(room);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            conn.Close();
            return roomlist;
        }

        public static ArrayList getAllOccupiedRooms(int room_level)
        {
            ArrayList roomlist = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                //comm.CommandText = "SELECT roomNo FROM Room WHERE roomLevel = @room_level AND roomStatus = 'Occupied'";
                //SELECT * FROM Room WHERE (roomNo IN (SELECT roomNo FROM RoomBooking WHERE bookingID NOT IN(SELECT bookingID FROM RoomCheckout)));
                comm.CommandText = "SELECT * FROM Room WHERE (roomNo IN (SELECT roomNo FROM RoomBooking WHERE bookingID NOT IN(SELECT bookingID FROM RoomCheckout))) AND roomLevel = @room_level";
                comm.Parameters.AddWithValue("@room_level", room_level);

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    int roomNo = (int)dr["roomNo"];
                    roomlist.Add(roomNo);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            conn.Close();
            return roomlist;
        }

        public static ArrayList getBookingDetailsByRoom(int roomNo)
        {
            ArrayList roomlist = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT roomNo, noOfAdults, noOfChildren FROM RoomBooking WHERE roomNo = @roomNo AND bookingID NOT IN(SELECT bookingID FROM RoomCheckout)";
                comm.Parameters.AddWithValue("@roomNo", roomNo);

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    roomOccupancy ro = new roomOccupancy();
                    ro.room_ID = (int)dr["roomNo"];
                    ro.adults_No = (int)dr["noOfAdults"];
                    ro.children_No = (int)dr["noOfChildren"];
                    roomlist.Add(ro);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            conn.Close();
            return roomlist;
        }

        public static ArrayList getBookingDetailsByDates(DateTime startdate, DateTime enddate)
        {
            ArrayList roomlist = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT roomNo, noOfAdults, noOfChildren FROM RoomBooking WHERE checkIn <  @enddate AND checkOut > @startdate";
                comm.Parameters.AddWithValue("@startdate", startdate);
                comm.Parameters.AddWithValue("@enddate", enddate);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    roomOccupancy ro = new roomOccupancy();
                    ro.room_ID = (int)dr["roomNo"];
                    ro.adults_No = (int)dr["noOfAdults"];
                    ro.children_No = (int)dr["noOfChildren"];
                    roomlist.Add(ro);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            conn.Close();
            return roomlist;
        }

        public static Occupancy getRoomOccupancyDetails(DateTime date)
        {
            Occupancy ou = new Occupancy();
            
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText =
                    "SELECT sum(noOfChildren), sum(noOfAdults), count(*), 50-count(*) "
                    + "from RoomBooking WHERE @date BETWEEN checkIn AND checkOut"
                    ;
                comm.Parameters.AddWithValue("@date", date);

                SqlDataReader dr = comm.ExecuteReader();
                
                while (dr.Read())
                {
                    
                    
                    if(DBNull.Value.Equals(dr[0]))
                    {
                        ou.Children = 0;
                    }
                    else
                    {
                        ou.Children = (int)dr[0];
                    }

                    if(DBNull.Value.Equals(dr[1])) //adults
                    {
                        ou.Adults = 0;
                    }
                    else
                    {
                        ou.Adults = (int)dr[1];
                    }

                    ou.OccupiedRooms = (int)dr[2];
                    ou.UnoccupiedRooms = (int)dr[3];
                    ou.Date = date;
                }
                
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            conn.Close();
            return ou;
        }

        public static ArrayList getShiftDay(string date)
        {
            ArrayList shiftlist = new ArrayList();

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText =
                    "SELECT * FROM Shift WHERE CONVERT(VARCHAR(25), startDateTime, 126) LIKE @date + '%'";

                comm.Parameters.AddWithValue("@date", date);

                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Shift sh = new Shift();
                    sh.shiftID = (int)dr["shiftID"];
                    sh.startDateTime = (DateTime)dr["startDateTime"];
                    sh.endDateTime = (DateTime)dr["endDateTime"];
                    sh.staffID = (int)dr["staffID"];
                    sh.dutyType = (int)dr["dutyType"];
                    shiftlist.Add(sh);
                }

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            conn.Close();
            return shiftlist;
        }

        public static ArrayList getShiftWeekMonth(DateTime startdate, DateTime enddate, string day)
        {
            ArrayList shiftlist = new ArrayList();

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText =
                    "SELECT * FROM Shift WHERE DATENAME(WEEKDAY, startDateTime) = @day"
                    + " AND YEAR(startDateTime) = @year"
                    + " AND MONTH(startDateTime) = @month"
                    + " AND DAY(startDateTime) between @startday AND @endday"
                    
                    ;
                comm.Parameters.AddWithValue("@day", day);
                comm.Parameters.AddWithValue("@year", startdate.Year);
                comm.Parameters.AddWithValue("@month", startdate.Month);
                comm.Parameters.AddWithValue("@startday", startdate.Day);
                comm.Parameters.AddWithValue("@endday", enddate.Day);
                

                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Shift sh = new Shift();
                    sh.shiftID = (int)dr["shiftID"];
                    sh.startDateTime = (DateTime)dr["startDateTime"];
                    sh.endDateTime = (DateTime)dr["endDateTime"];
                    sh.staffID = (int)dr["staffID"];
                    sh.dutyType = (int)dr["dutyType"];
                    shiftlist.Add(sh);
                }

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            conn.Close();
            return shiftlist;
        }
    }

}